package com.stray.crawl.model.dao;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.Table;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteDataSource;

import com.stray.crawl.utils.IOUtils;

public class SimpleEntityManager {

	private static final Log log = LogFactory.getLog(SimpleEntityManager.class);

	private Connection conn;

	public SimpleEntityManager(String url) throws SQLException {
		SQLiteConfig config = new SQLiteConfig();

		config.setSharedCache(true);
		config.enableRecursiveTriggers(true);

		SQLiteDataSource sqlds = new SQLiteDataSource(config);
		sqlds.setUrl(url);

		conn = sqlds.getConnection();
	}

	public void close() {
		IOUtils.closeQuietly(conn);
	}

	public <T> List<T> find(Class<T> clazz) throws SQLException, InstantiationException, IllegalAccessException {
		return find(clazz, null, false);
	}

	public <T> T find(Class<T> clazz, Number id) throws SQLException, InstantiationException, IllegalAccessException {
		List<T> list = find(clazz, "id", id);
		return !list.isEmpty() ? list.get(0) : null;
	}

	public <T> T findLazyLoad(Class<T> clazz, Number id) throws SQLException, InstantiationException, IllegalAccessException {
		Map<String, String> params = new HashMap<String, String>();
		params.put("id", String.valueOf(id));

		List<T> list = find(clazz, params, true);
		return !list.isEmpty() ? list.get(0) : null;
	}

	public <T> List<T> find(Class<T> clazz, String key, Object value) throws SQLException, InstantiationException, IllegalAccessException {
		HashMap<String, String> params = new HashMap<String, String>();
		params.put(key, value != null ? String.valueOf(value) : "");

		return find(clazz, params, false);
	}

	public <T> List<T> find(Class<T> clazz, Map<String, String> params, boolean initRelation) throws SQLException, InstantiationException, IllegalAccessException {
		String table = getTableAnnotationName(clazz);
		StringBuffer buf = new StringBuffer();

		buf.append("SELECT t.* FROM `" + table + "` t ");

		if (params != null && !params.isEmpty()) {
			buf.append("WHERE ");
			for (Entry<String, String> entry : params.entrySet()) {
				String key = entry.getKey();
				String val = entry.getValue();

				buf.append("t.`" + key + "`");
				buf.append(" = '" + val + "'");
			}
		}

		Statement stat = null;
		ResultSet rs = null;
		List<T> result = new ArrayList<T>();

		try {
			stat = conn.createStatement();
			log.debug("Executing SQL : " + buf.toString());
			rs = stat.executeQuery(buf.toString());

			Field[] fields = clazz.getDeclaredFields();

			while (rs.next()) {
				T t = clazz.newInstance();
				Field idField = null;

				for (Field field : fields) {
					Column aCol = field.getAnnotation(Column.class);
					Id aId = field.getAnnotation(Id.class);
					JoinColumn aJc = field.getAnnotation(JoinColumn.class);

					Object value = null;
					if (aId != null) {
						String name = field.getName();
						value = rs.getObject(name);
						idField = field;
					} else if (aCol != null) {
						String name = aCol.name();
						name = name != null && name.length() > 0 ? name : field.getName();

						if (field.getType().getCanonicalName().equals("boolean")) {
							value = rs.getObject(name);
							value = value != null ? Boolean.valueOf(value.toString()) : false;
						} else {
							value = rs.getObject(name);
						}
					} else if (aJc != null && initRelation) {
						String name = aJc.name();
						Object object = rs.getObject(idField.getName());

						Map<String, String> map = new HashMap<String, String>();
						if (object != null) {
							map.put(name, object.toString());
						}

						ParameterizedType type = (ParameterizedType) field.getGenericType();
						Class<?> genericClazz = (Class<?>) type.getActualTypeArguments()[0];

						value = find(genericClazz, map, initRelation);
					}

					if (value != null) {
						field.setAccessible(true);
						field.set(t, value);
					}
				}

				result.add(t);
			}
		} finally {
			IOUtils.closeQuietly(stat);
			IOUtils.closeQuietly(rs);
		}

		return result;
	}

	public <T> T persist(T entity) throws IllegalAccessException, SQLException {
		return persist(entity, null);
	}

	public <T> T persist(T entity, Map<String, String> extraParams) throws IllegalAccessException, SQLException {
		Class<? extends Object> clazz = entity.getClass();

		Map<String, String> params = new HashMap<String, String>();
		if (extraParams != null && !extraParams.isEmpty()) {
			params.putAll(extraParams);
		}
		Field[] fields = clazz.getDeclaredFields();
		Field idField = null;
		Long id = null;

		for (Field field : fields) {
			Column aCol = field.getAnnotation(Column.class);
			Id aId = field.getAnnotation(Id.class);

			if (aId != null) {
				idField = field;
			}
			if (aCol != null) {
				String name = aCol.name();
				name = name != null && name.length() > 0 ? name : field.getName();

				field.setAccessible(true);

				Object obj = field.get(entity);
				if (obj != null) {
					params.put(name, obj.toString());
				}
			}
		}

		if (!params.isEmpty()) {
			idField.setAccessible(true);
			id = (Long) idField.get(entity);
			String table = getTableAnnotationName(entity.getClass());

			if (id != null && id != 0) {
				update(table, id, params);
			} else {
				id = insert(table, params);
				idField.set(entity, id);
			}
		}

		for (Field field : fields) {
			OneToMany otmAnn = field.getAnnotation(OneToMany.class);
			JoinColumn jcAnn = field.getAnnotation(JoinColumn.class);

			if (otmAnn != null && jcAnn != null) {
				field.setAccessible(true);
				Collection<?> list = (Collection<?>) field.get(entity);
				if (list != null && !list.isEmpty()) {
					Map<String, String> newParams = new HashMap<String, String>();
					newParams.put(jcAnn.name(), String.valueOf(id));

					for (Object obj : list) {
						persist(obj, newParams);
					}
				}
			}
		}

		return entity;
	}

	public long insert(String table, Map<String, String> params) throws SQLException {
		Statement stat = null;
		ResultSet rs = null;

		try {
			StringBuffer buf = new StringBuffer();

			buf.append("INSERT INTO `" + table + "` (");
			for (String key : params.keySet()) {
				buf.append("`" + key + "`,");
			}

			buf.deleteCharAt(buf.length() - 1);

			buf.append(") VALUES (");
			for (String val : params.values()) {
				buf.append("'" + val + "',");
			}

			buf.deleteCharAt(buf.length() - 1);
			buf.append(")");

			log.debug("Executing SQL : " + buf.toString());
			stat = conn.createStatement();
			stat.executeUpdate(buf.toString());

			rs = stat.getGeneratedKeys();
			return rs.getLong(1);
		} finally {
			IOUtils.closeQuietly(stat);
			IOUtils.closeQuietly(rs);
		}
	}

	public void update(String table, long id, Map<String, String> params) throws SQLException {
		Statement stat = null;

		try {
			StringBuffer buf = new StringBuffer();

			buf.append("UPDATE `" + table + "` SET ");
			Set<Entry<String, String>> entrySet = params.entrySet();
			for (Entry<String, String> entry : entrySet) {
				String key = entry.getKey();
				String val = entry.getValue();

				buf.append("    `" + key + "` = '" + val + "',");
			}
			buf.deleteCharAt(buf.length() - 1);
			buf.append(" WHERE `id` = " + id);

			log.debug("Executing SQL : " + buf.toString());
			stat = conn.createStatement();
			stat.executeUpdate(buf.toString());
		} finally {
			IOUtils.closeQuietly(stat);
		}
	}

	public Object querySingleResult(String sql) throws SQLException {
		List<?> list = query(sql);
		return !list.isEmpty() ? list.get(0) : null;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<?> query(String sql) throws SQLException {
		List objsList;

		Statement stat = null;
		ResultSet rs = null;

		try {
			stat = conn.createStatement();
			log.debug("Querying SQL : " + sql);
			rs = stat.executeQuery(sql);
			int count = rs.getMetaData().getColumnCount();

			if (count > 1) {
				objsList = new ArrayList<Object[]>();

				while (rs.next()) {
					Object[] objs = new Object[count];

					for (int i = 0; i < count; i++) {
						objs[i] = rs.getObject(i + 1);
					}

					objsList.add(objs);
				}
			} else {
				objsList = new ArrayList<Object>();

				while (rs.next()) {
					objsList.add(rs.getObject(1));
				}
			}

			return objsList;
		} finally {
			IOUtils.closeQuietly(rs);
			IOUtils.closeQuietly(stat);
		}
	}

	public void execute(String sql) throws SQLException {
		Statement stat = null;

		try {
			stat = conn.createStatement();
			log.debug("Executing SQL : " + sql);
			stat.executeUpdate(sql);
		} finally {
			IOUtils.closeQuietly(stat);
		}
	}
	
	public void clearIds(Object entity) throws IllegalArgumentException, IllegalAccessException {
		Class<? extends Object> clazz = entity.getClass();
		Field[] fields = clazz.getDeclaredFields();

		for (Field field : fields) {
			if (field.getAnnotation(Id.class) != null) {
				field.setAccessible(true);
				field.set(entity, 0L);
			} else if (field.getAnnotation(OneToMany.class) != null && field.getAnnotation(JoinColumn.class) != null) {
				field.setAccessible(true);
				Collection<?> list = (Collection<?>) field.get(entity);
				
				if (list != null && !list.isEmpty()) {
					for (Object obj : list) {
						clearIds(obj);
					}
				}
			}
		}
	}

	private String getTableAnnotationName(Class<?> clazz) {
		Table annotation = clazz.getAnnotation(Table.class);

		if (annotation == null) {
			throw new IllegalArgumentException("No annotation @Table found. ");
		} else {
			String name = annotation.name();
			return name != null && name.length() > 0 ? name : clazz.getSimpleName().toUpperCase();
		}
	}

}